package com.xiyu.service.repository.customer.info;

import com.xiyu.service.model.customer.contact.*;
import com.xiyu.service.model.customer.info.*;
import com.xiyu.service.model.infra.data.DictCountryFetcher;
import com.xiyu.service.model.system.dept.SystemDept;
import com.xiyu.service.model.system.dept.SystemDeptFetcher;
import com.xiyu.service.model.system.user.SystemUser;
import com.xiyu.service.model.system.user.SystemUserFetcher;
import com.xiyu.service.vo.customer.customer.CustomerPageQueryInput;
import org.babyfish.jimmer.spring.repository.JRepository;
import org.babyfish.jimmer.Page;
import org.babyfish.jimmer.sql.JoinType;
import org.babyfish.jimmer.sql.ast.Expression;
import org.babyfish.jimmer.sql.ast.Predicate;
import org.babyfish.jimmer.sql.ast.tuple.Tuple2;
import org.babyfish.jimmer.sql.ast.tuple.Tuple3;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.util.Optional;
import java.util.List;

public interface CustomerInfoRepository extends JRepository<CustomerInfo, Long> {
    CustomerInfoTable customerInfoTable = CustomerInfoTable.$;

    default Page<CustomerInfo> pageQuery(CustomerPageQueryInput inputVO, List<Long> ownerUserIds, List<Long> ownerDeptIds, boolean isLeader){
        return sql().createQuery(customerInfoTable)
                .whereIf(inputVO.getGrade() != null, customerInfoTable.grade().eq(inputVO.getGrade()))
                .whereIf(StringUtils.hasText(inputVO.getName()), () -> customerInfoTable.name().like(inputVO.getName()))
                .whereIf(inputVO.getStage() != null, customerInfoTable.stage().eq(inputVO.getStage()))
                .whereIf(inputVO.getSource() != null, customerInfoTable.source().eq(inputVO.getSource()))
                .whereIf(StringUtils.hasText(inputVO.getCustomerNo()), () -> customerInfoTable.customerNo().like(inputVO.getCustomerNo()))
                .whereIf(inputVO.getState() != null, customerInfoTable.state().eq(inputVO.getState()))
                .where(customerInfoTable.isRecycle().eq(inputVO.getIsRecycle()))
                .where(customerInfoTable.isFallPool().eq(inputVO.getIsFallPool()))
                //  客户管理页面 部门领导可以看当前部门的所有员工的  员工只能看自己的  公海管理页面 部门领导可以看所有的 员工只能看自己部门的
                .whereIf(!inputVO.getIsFallPool(), customerInfoTable.ownerId().in(ownerUserIds))
                .whereIf(inputVO.getIsFallPool() && !isLeader, customerInfoTable.deptId().in(ownerDeptIds))
                .orderBy(customerInfoTable.id().desc())
                .select(customerInfoTable.fetch(CustomerInfoFetcher.$.allTableFields()
                        .mainContact(CustomerContactFetcher.$.allTableFields())
                        .country(DictCountryFetcher.$.allTableFields())
                        .images(CustomerImageFetcher.$.url(), it -> it.filter(args -> args.orderBy(args.getTable().id())).limit(1))
                        .dept(SystemDeptFetcher.$.name())
                        .owner(SystemUserFetcher.$.nickname())
                ))
                .fetchPage(inputVO.getPageNo() - 1, inputVO.getPageSize());
    }

    default List<CustomerInfo> findDetailByIds(List<Long> ids){
        return sql().createQuery(customerInfoTable)
                .where(customerInfoTable.id().in(ids))
                .select(customerInfoTable.fetch(
                        CustomerInfoFetcher.$.allTableFields()
                                .country(DictCountryFetcher.$.allScalarFields())
                                .contacts(CustomerContactFetcher.$.allTableFields()
                                        .mails(CustomerContactMailFetcher.$.allTableFields())
                                        .socialMedias(CustomerContactSocialMediaFetcher.$.allTableFields())
                                        .phones(CustomerContactPhoneFetcher.$.allTableFields())
                                )
                                .dept(SystemDeptFetcher.$.name())
                                .owner(SystemUserFetcher.$.nickname())
                                .websites(CustomerWebsiteFetcher.$.allTableFields())
                )).execute();
    }

    default List<Long> duplicateCheckByMailSuffix(String mailSuffix, boolean isFallPool, List<Long> ownerUserIds, List<Long> ownerDeptIds, boolean isLeader){
        return sql().createQuery(customerInfoTable)
                .where(customerInfoTable.asTableEx().contacts().mails().mailAddress().like(mailSuffix))
                .where(customerInfoTable.isRecycle().eq(false))
                .whereIf(!isFallPool, customerInfoTable.ownerId().in(ownerUserIds))
                .whereIf(isFallPool && !isLeader, customerInfoTable.deptId().in(ownerDeptIds))
                .orderBy(customerInfoTable.id().desc())
                .select(customerInfoTable.id())
                .distinct()
                .execute();
    }

    default List<Long> duplicateCheck(String searchText, List<String> settingColumnName, boolean isFallPool, List<Long> ownerUserIds, List<Long> ownerDeptIds, boolean isLeader){
        return sql().createQuery(customerInfoTable)
                .where(Predicate.or(
                        settingColumnName.contains("name")? customerInfoTable.name().like(searchText) : null,
                        settingColumnName.contains("short_name")? customerInfoTable.shortName().like(searchText) : null,
                        settingColumnName.contains("address")? customerInfoTable.address().like(searchText) : null,
                        settingColumnName.contains("website")? customerInfoTable.asTableEx().websites(JoinType.LEFT).website().like(searchText) : null,
                        settingColumnName.contains("custom_field1")? customerInfoTable.customField1().like(searchText) : null,
                        settingColumnName.contains("custom_field2")? customerInfoTable.customField2().like(searchText) : null,
                        settingColumnName.contains("custom_field3")? customerInfoTable.customField3().like(searchText) : null,
                        settingColumnName.contains("custom_field4")? customerInfoTable.customField4().like(searchText) : null,
                        settingColumnName.contains("custom_field5")? customerInfoTable.customField5().like(searchText) : null,
                        settingColumnName.contains("custom_field6")? customerInfoTable.customField6().like(searchText) : null,
                        settingColumnName.contains("custom_field7")? customerInfoTable.customField7().like(searchText) : null,
                        settingColumnName.contains("custom_field8")? customerInfoTable.customField8().like(searchText) : null,
                        settingColumnName.contains("custom_field9")? customerInfoTable.customField9().like(searchText) : null,
                        settingColumnName.contains("custom_field10")? customerInfoTable.customField10().like(searchText) : null,
                        settingColumnName.contains("custom_field11")? customerInfoTable.customField11().like(searchText) : null,
                        settingColumnName.contains("custom_field12")? customerInfoTable.customField12().like(searchText) : null,
                        settingColumnName.contains("custom_field13")? customerInfoTable.customField13().like(searchText) : null,
                        settingColumnName.contains("custom_field14")? customerInfoTable.customField14().like(searchText) : null,
                        settingColumnName.contains("custom_field15")? customerInfoTable.customField15().like(searchText) : null,
                        settingColumnName.contains("custom_field16")? customerInfoTable.customField16().like(searchText) : null,
                        settingColumnName.contains("custom_field17")? customerInfoTable.customField17().like(searchText) : null,
                        settingColumnName.contains("custom_field18")? customerInfoTable.customField18().like(searchText) : null,
                        settingColumnName.contains("custom_field19")? customerInfoTable.customField19().like(searchText) : null,
                        settingColumnName.contains("custom_field20")? customerInfoTable.customField20().like(searchText) : null,
                        settingColumnName.contains("contact_name")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).name().like(searchText) : null,
                        settingColumnName.contains("contact_nick_name")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).nickName().like(searchText) : null,
                        settingColumnName.contains("contact_phone")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).phones(JoinType.LEFT).phoneNo().like(searchText) : null,
                        settingColumnName.contains("contact_mail")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).mails(JoinType.LEFT).mailAddress().like(searchText) : null,
                        settingColumnName.contains("contact_social_media")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).socialMedias(JoinType.LEFT).socialAccount().like(searchText) : null,
                        settingColumnName.contains("contact_address")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).address().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field1")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField1().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field2")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField2().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field3")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField3().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field4")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField4().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field5")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField5().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field6")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField6().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field7")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField7().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field8")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField8().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field9")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField9().like(searchText) : null,
                        settingColumnName.contains("contact_custom_field10")? customerInfoTable.asTableEx().contacts(JoinType.LEFT).customField10().like(searchText) : null
                ))
                .where(customerInfoTable.isRecycle().eq(false))
                .whereIf(!isFallPool, customerInfoTable.ownerId().in(ownerUserIds))
                .whereIf(isFallPool && !isLeader, customerInfoTable.deptId().in(ownerDeptIds))
                .orderBy(customerInfoTable.id().desc())
                .select(customerInfoTable.id())
                .distinct()
                .execute();
    }

    default Optional<CustomerInfo> singleGetDetail(Long id){
        return sql().createQuery(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .select(customerInfoTable.fetch(
                        CustomerInfoFetcher.$.allTableFields()
                                .images(CustomerImageFetcher.$.allTableFields())
                                .websites(CustomerWebsiteFetcher.$.allTableFields())
                                .contacts(CustomerContactFetcher.$.allTableFields()
                                        .mails(CustomerContactMailFetcher.$.allTableFields())
                                        .phones(CustomerContactPhoneFetcher.$.allTableFields())
                                        .socialMedias(CustomerContactSocialMediaFetcher.$.allTableFields())
                                        .businessCards(CustomerContactBusinessCardFetcher.$.allTableFields())
                                )
                ))
                .fetchOptional();
    }

    default void updateMainContactId(Long id, Long mainContactId){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.mainContactId(), mainContactId)
                .execute();

    }

    default void batchUpdateIsRecycle(List<Long> ids, Boolean isRecycle){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().in(ids))
                .set(customerInfoTable.isRecycle(), isRecycle)
                .execute();
    }

    default void batchUpdateDeletedTime(List<Long> ids){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().in(ids))
                .set(customerInfoTable.deletedTime(), LocalDateTime.now())
                .execute();
    }

    default void updateFallInPool(Long id, String reason){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.isFallPool(), true)
                .set(customerInfoTable.comingFallPoolTime(), (LocalDateTime) null)
                .set(customerInfoTable.fallPoolReason(), reason)
                .execute();
    }

    default void allocation(Long id, Long ownerId, Long deptId){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.isFallPool(), false)
                .set(customerInfoTable.ownerId(), ownerId)
                .set(customerInfoTable.deptId(), deptId)
                .execute();
    }

    default Tuple2<Long, Long> countByOwnerId(Long ownerId, Long stateDealOptionId){
        return sql().createQuery(customerInfoTable)
                .where(customerInfoTable.ownerId().eq(ownerId))
                .where(customerInfoTable.isRecycle().eq(false))
                .where(customerInfoTable.isFallPool().eq(false))
                .select(customerInfoTable.id().count(),
                        Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e = %v, 0, 1)),0)", customerInfoTable.state(), stateDealOptionId)
                ).fetchOptional().orElse(null);
    }

    default List<Tuple3<Long, LocalDateTime, Long>> keyTrendQuery(List<Long> userOwnerIds, List<Long> ownerIds, LocalDateTime[] times){
        return sql().createQuery(customerInfoTable)
                .whereIf(!ownerIds.isEmpty(), customerInfoTable.ownerId().in(ownerIds))
                .where(customerInfoTable.ownerId().in(userOwnerIds))
                .where(customerInfoTable.createTime().between(times[0], times[1]))
                .where(customerInfoTable.isRecycle().eq(false))
                .groupBy(customerInfoTable.ownerId(), Expression.any().sql(LocalDateTime.class, "DATE_FORMAT(%e, '%Y-%m-%d')", customerInfoTable.createTime()))
                .select(customerInfoTable.ownerId(), Expression.any().sql(LocalDateTime.class, "DATE_FORMAT(%e, '%Y-%m-%d')", customerInfoTable.createTime()), customerInfoTable.id().count())
                .execute();
    }

    default List<Tuple3<Long, Long, Long>> dealConvertQuery(List<Long> userOwnerIds, List<Long> ownerIds, Long stateDealOptionId){
        return sql().createQuery(customerInfoTable)
                .whereIf(ownerIds != null && !ownerIds.isEmpty(), () -> customerInfoTable.ownerId().in(ownerIds))
                .where(customerInfoTable.ownerId().in(userOwnerIds))
                .where(customerInfoTable.isRecycle().eq(false))
                .groupBy(customerInfoTable.ownerId())
                .select(customerInfoTable.ownerId(), Expression.any().sql(Long.class,"IFNULL(SUM(IF(%e = %v, 0, 1)),0)", customerInfoTable.state(), stateDealOptionId), customerInfoTable.id().count())
                .execute();
    }

    default void updateLastFollowTime(Long id){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.lastFollowTime(), LocalDateTime.now())
                .execute();
    }

    default void updateComingFallTime(Long id, LocalDateTime comingFallTime, Long comingFallRuleId){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.comingFallPoolTime(), comingFallTime)
                .set(customerInfoTable.comingFallPoolRuleId(), comingFallRuleId)
                .execute();
    }

    default void updateState(Long id, Long state){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.state(), state)
                .execute();
    }

    default List<Long> findAllNotInPool(Long dealStateId){
        return sql().createQuery(customerInfoTable)
                .where(customerInfoTable.isFallPool().eq(false))
                .where(customerInfoTable.isRecycle().eq(false))
                .where(customerInfoTable.state().ne(dealStateId))
                .orderBy(customerInfoTable.ownerId(), customerInfoTable.id())
                .select(customerInfoTable.id())
                .execute();
    }

    default void updateOwnerId(Long id, Long ownerId){
        sql().createUpdate(customerInfoTable)
                .where(customerInfoTable.id().eq(id))
                .set(customerInfoTable.ownerId(), ownerId)
                .execute();
    }

    Optional<CustomerInfo> findByName(String name);

    Optional<CustomerInfo> findByCustomerNo(String customerNo);


}